<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

class DataBLU {

    private $db;
	private $_satker;
	private $_nmsatker;
	private $_kppn;
	private $_rumpun;
	private $_januari;
	private $_februari;
	private $_maret;
	private $_april;
	private $_mei;
	private $_juni;
	private $_juli;
	private $_agustus;
	private $_september;
	private $_oktober;
	private $_november;
	private $_desember;
	private $_invoice_num;
	private $_invoice_date;
	private $_check_number;
	private $_check_date;
	private $_check_amount;
	private $_pendapatan;
	private $_belanja;
	private $_akun;	
	private $_program;
	private $_output;
	private $_saldo_awal;
	private $_saldo_akhir;
	private $_belanja_51;
    private $_belanja_52;
    private $_belanja_53;
    private $_belanja_54;
    private $_belanja_55;
    private $_belanja_56;
    private $_belanja_57;
    private $_belanja_58;
    private $_belanja_59;
    private $_belanja_71;
    private $_belanja_61;
	private $_pagu_51;
    private $_pagu_52;
    private $_pagu_53;
    private $_pagu_54;
    private $_pagu_55;
    private $_pagu_56;
    private $_pagu_57;
    private $_pagu_58;
    private $_pagu_59;
    private $_pagu_71;
    private $_pagu_61;
	private $_ba;
	private $_pagu;
	private $_dipa;
	private $_realisasi;
	private $_deskripsi;
	private $_jumlah_15;
	private $_jumlah_14;
	private $_flag;
	private $_selisih;
	private $_persentase;
	private $_updownflag;
	private $_jenis;
	private $_nama_ba;
	private $_baes1;
	private $_nama_baes1;
	private $_tanggal;
	private $_nama_kppn;
	private $_kanwil;
	private $_sumber_dana;
    //private $_table1 = 'SP3B_BLU';
	//private $_table2 = 'DAFTAR_SP3B_BLU';
	private $_table3 = 'CARI_SP3B_BLU';
	private $_table4 = 'GL_BALANCES_V';
    public $registry;

    /*
     * konstruktor
     */

    public function __construct($registry = Registry) {
        $this->db = $registry->db;
        $this->registry = $registry;
		
		if ((''.Session::get('ta')) == date("Y")) {
            $this->_table1 = 'SP3B_BLU';
            $this->_table2 = 'DAFTAR_SP3B_BLU';
        } else {
            $this->_table1 = 'SP3B_BLU_TL';
            $this->_table2 = 'DAFTAR_SP3B_BLU_TL';
        }
    }

    /*
     * mendapatkan data dari tabel Data Tetap
     * @param limit batas default null
     * return array objek Data Tetap */

    public function get_rekap_sp3b($filter) {
        Session::get('id_user');
        $sql = "SELECT *
				FROM "
                . $this->_table1 . "
				WHERE 
				1=1"

        ;

        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }


        $sql .= " ORDER BY SATKER ASC";
        //var_dump ($sql);

        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['SATKER']);
            $d_data->set_nmsatker($val['NMSATKER']);
			$d_data->set_kppn($val['KDKPPN']);
			$d_data->set_rumpun($val['RUMPUN']);
			$d_data->set_januari($val['JAN']);
			$d_data->set_februari($val['FEB']);
			$d_data->set_maret($val['MAR']);
			$d_data->set_april($val['APR']);
			$d_data->set_mei($val['MAY']);
			$d_data->set_juni($val['JUN']);
			$d_data->set_juli($val['JUL']);
			$d_data->set_agustus($val['AUG']);
			$d_data->set_september($val['SEP']);
			$d_data->set_oktober($val['OKT']);
			$d_data->set_november($val['NOV']);
			$d_data->set_desember($val['DES']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_daftar_sp3b($filter) {
        Session::get('id_user');
        $sql = "SELECT *
				FROM "
                . $this->_table2 . "
				WHERE 
				1=1"

        ;

        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }


        $sql .= " ORDER BY INVOICE_NUM";
        //var_dump ($sql);

        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_invoice_num($val['INVOICE_NUM']);
            $d_data->set_invoice_date($val['INVOICE_DATE']);
			$d_data->set_check_number($val['CHECK_NUMBER']);
			$d_data->set_check_date($val['CHECK_DATE']);
			$d_data->set_check_amount($val['CHECK_AMOUNT']);
			$d_data->set_pendapatan($val['PENDAPATAN']);
			$d_data->set_belanja($val['BELANJA']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_cari_sp3b($filter) {
        Session::get('id_user');
        $sql = "SELECT *
				FROM "
                . $this->_table3 . "
				WHERE 
				1=1"

        ;

        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }


        $sql .= " ORDER BY INVOICE_NUM";
        //var_dump ($sql);

        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_invoice_num($val['INVOICE_NUM']);
            $d_data->set_invoice_date($val['INVOICE_DATE']);
			$d_data->set_check_number($val['CHECK_NUMBER']);
			$d_data->set_check_date($val['CHECK_DATE']);
			$d_data->set_check_amount($val['CHECK_AMOUNT']);
			$d_data->set_pendapatan($val['PENDAPATAN']);
			$d_data->set_belanja($val['BELANJA']);
			$d_data->set_akun($val['SEGMENT3']);
			$d_data->set_kppn($val['KDKPPN']);
			$d_data->set_satker($val['SEGMENT1']);
			$d_data->set_program($val['SEGMENT4']);
			$d_data->set_output($val['SEGMENT5']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_kdsatker_blu($satker) {
        Session::get('id_user');
        if ((''.Session::get('ta')) == date("Y")) {
            $tabel = 'T_SATKER';
        } else {
            $tabel = 'T_SATKER_TL';
        }
        
        $sql = "SELECT A.KDSATKER ,A.NMSATKER ,A.BA ,B.NMBA
				FROM " . $tabel . " A,
				T_BA B
				WHERE
				A.BA=B.KDBA
				AND A.KDSATKER = '".$satker."'"
				

        ;

        $no = 0;
        /* foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        } */


        //$sql .= " ORDER BY INVOICE_NUM";
        //var_dump ($sql);

        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
            $d_data->set_nmsatker($val['NMSATKER']);
			$d_data->set_kppn($val['BA']);			
			$d_data->set_pendapatan($val['NMBA']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	
	public function get_satker_blu($filter) {
        Session::get('id_user');
        
        $sql = "SELECT KDSATKER ,UPPER(NMSATKER) NMSATKER
				FROM RUMPUN_BLU "
        ;

        $no = 0;
        /* foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        } */


        $sql .= " ORDER BY KDSATKER";
        //var_dump ($sql);

        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
            $d_data->set_nmsatker($val['NMSATKER']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	
	public function get_list_ba($filter) {
        Session::get('id_user');
        
        $sql = "SELECT KDBA ,UPPER(NMBA) NMBA
				FROM T_BA "
        ;

        $no = 0;
        /* foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        } */


        $sql .= " ORDER BY KDBA";
        //var_dump ($sql);

        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_ba($val['KDBA']);
            $d_data->set_nama_ba($val['NMBA']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_realisasi_blu($filter) {
        Session::get('id_user');
        $sql = "select a.satker
				, substr(a.program,1,3) BA
				--, a.kppn
				, c.nmsatker
				, c.rumpun
				, sum(a.budget_amt) Pagu
				, sum(a.actual_amt) Total_realisasi
				, sum(decode(substr(a.akun,1,2),'51',a.budget_amt,0)) pagu_51
				, sum(decode(substr(a.akun,1,2),'52',a.budget_amt,0)) pagu_52
				, sum(decode(substr(a.akun,1,2),'53',a.budget_amt,0)) pagu_53
				, sum(decode(substr(a.akun,1,2),'54',a.budget_amt,0)) pagu_54
				, sum(decode(substr(a.akun,1,2),'55',a.budget_amt,0)) pagu_55
				, sum(decode(substr(a.akun,1,2),'56',a.budget_amt,0)) pagu_56
				, sum(decode(substr(a.akun,1,2),'57',a.budget_amt,0)) pagu_57
				, sum(decode(substr(a.akun,1,2),'58',a.budget_amt,0)) pagu_58
				, sum(decode(substr(a.akun,1,2),'59',a.budget_amt,0)) pagu_59
				, sum(decode(substr(a.akun,1,1),'6',a.budget_amt,0)) pagu_61
				, sum(decode(substr(a.akun,1,2),'51',a.actual_amt,0)) belanja_51
				, sum(decode(substr(a.akun,1,2),'52',a.actual_amt,0)) belanja_52
				, sum(decode(substr(a.akun,1,2),'53',a.actual_amt,0)) belanja_53
				, sum(decode(substr(a.akun,1,2),'54',a.actual_amt,0)) belanja_54
				, sum(decode(substr(a.akun,1,2),'55',a.actual_amt,0)) belanja_55
				, sum(decode(substr(a.akun,1,2),'56',a.actual_amt,0)) belanja_56
				, sum(decode(substr(a.akun,1,2),'57',a.actual_amt,0)) belanja_57
				, sum(decode(substr(a.akun,1,2),'58',a.actual_amt,0)) belanja_58
				, sum(decode(substr(a.akun,1,2),'59',a.actual_amt,0)) belanja_59
				, sum(decode(substr(a.akun,1,1),'6',a.actual_amt,0)) belanja_61
				, sum(ENCUMBRANCE_AMT) encumbrance 
				FROM "
                . $this->_table4 . " a,
                --t_satker_blu  b ,
				rumpun_blu c
				where 1=1
				and a.budget_type = '2' 
				--and a.satker=b.kdsatker
				and a.satker=c.kdsatker 				
				--and a.kppn=b.kdkppn
				AND SUBSTR(a.akun,1,1) in ('5','6')
				and nvl(a.budget_amt,0) + nvl(a.actual_amt,0) + nvl(a.encumbrance_amt,0) > 0
				
				"
        ;
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }

        $sql .= " group by a.satker ,c.nmsatker,  substr(a.program,1,3)	, c.rumpun ";
        $sql .= " ORDER by a.satker ";

        //var_dump ($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['SATKER']);
            $d_data->set_kppn($val['KPPN']);
            $d_data->set_ba($val['BA']);
            $d_data->set_pagu($val['PAGU']);
            $d_data->set_dipa($val['NMSATKER']);
			$d_data->set_rumpun($val['RUMPUN']);
			$d_data->set_pagu_51($val['PAGU_51']);
            $d_data->set_pagu_52($val['PAGU_52']);
            $d_data->set_pagu_53($val['PAGU_53']);
            $d_data->set_pagu_54($val['PAGU_54']);
            $d_data->set_pagu_55($val['PAGU_55']);
            $d_data->set_pagu_56($val['PAGU_56']);
            $d_data->set_pagu_57($val['PAGU_57']);
            $d_data->set_pagu_58($val['PAGU_58']);
            $d_data->set_pagu_59($val['PAGU_59']);
			$d_data->set_pagu_61($val['PAGU_61']);
            $d_data->set_belanja_51($val['BELANJA_51']);
            $d_data->set_belanja_52($val['BELANJA_52']);
            $d_data->set_belanja_53($val['BELANJA_53']);
            $d_data->set_belanja_54($val['BELANJA_54']);
            $d_data->set_belanja_55($val['BELANJA_55']);
            $d_data->set_belanja_56($val['BELANJA_56']);
            $d_data->set_belanja_57($val['BELANJA_57']);
            $d_data->set_belanja_58($val['BELANJA_58']);
            $d_data->set_belanja_59($val['BELANJA_59']);
			$d_data->set_belanja_61($val['BELANJA_61']);
			$d_data->set_realisasi($val['TOTAL_REALISASI']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_realisasi_belanja_blu($filter) {
        Session::get('id_user');
        $sql = "select a.satker
				, substr(a.program,1,3) BA
				, a.kppn
				--, b.nmsatker
				, c.nmsatker
				, c.rumpun
				, sum(a.budget_amt) Pagu
				, sum(a.actual_amt) Total_realisasi
				, sum(decode(a.akun,'525111',a.budget_amt,0)) pagu_51
				, sum(decode(a.akun,'525112',a.budget_amt,0)) pagu_52
				, sum(decode(a.akun,'525113',a.budget_amt,0)) pagu_53
				, sum(decode(a.akun,'525114',a.budget_amt,0)) pagu_54
				, sum(decode(a.akun,'525115',a.budget_amt,0)) pagu_55
				, sum(decode(a.akun,'525116',a.budget_amt,0)) pagu_56
				, sum(decode(a.akun,'525119',a.budget_amt,0)) pagu_57				
				, sum(decode(a.akun,'525111',a.actual_amt,0)) belanja_51
				, sum(decode(a.akun,'525112',a.actual_amt,0)) belanja_52
				, sum(decode(a.akun,'525113',a.actual_amt,0)) belanja_53
				, sum(decode(a.akun,'525114',a.actual_amt,0)) belanja_54
				, sum(decode(a.akun,'525115',a.actual_amt,0)) belanja_55
				, sum(decode(a.akun,'525116',a.actual_amt,0)) belanja_56
				, sum(decode(a.akun,'525119',a.actual_amt,0)) belanja_57				
				, sum(ENCUMBRANCE_AMT) encumbrance 
				FROM rumpun_blu c, "
                . $this->_table4 . " a
                --t_satker_blu  b ,
				
				where 1=1
				and a.budget_type = '2' 
				--and a.satker=b.kdsatker
				and a.satker=c.kdsatker		(+)		
				and c.nmsatker is not null
				--and a.kppn=b.kdkppn
				AND SUBSTR(a.akun,1,1) in ('5','6')
				and substr(a.akun,1,3) = '525'
				and nvl(a.budget_amt,0) + nvl(a.actual_amt,0) + nvl(a.encumbrance_amt,0) > 0
				
				"
        ;
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }

        $sql .= " group by a.satker ,c.nmsatker, a.kppn, substr(a.program,1,3), c.rumpun ";
        $sql .= " ORDER by a.satker ";

        //var_dump ($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['SATKER']);
            $d_data->set_kppn($val['KPPN']);
            $d_data->set_ba($val['BA']);
            $d_data->set_pagu($val['PAGU']);
            $d_data->set_dipa($val['NMSATKER']);
			$d_data->set_rumpun($val['RUMPUN']);
			$d_data->set_pagu_51($val['PAGU_51']);
            $d_data->set_pagu_52($val['PAGU_52']);
            $d_data->set_pagu_53($val['PAGU_53']);
            $d_data->set_pagu_54($val['PAGU_54']);
            $d_data->set_pagu_55($val['PAGU_55']);
            $d_data->set_pagu_56($val['PAGU_56']);
            $d_data->set_pagu_57($val['PAGU_57']);
            $d_data->set_pagu_58($val['PAGU_58']);
            $d_data->set_pagu_59($val['PAGU_59']);
			$d_data->set_pagu_61($val['PAGU_61']);
            $d_data->set_belanja_51($val['BELANJA_51']);
            $d_data->set_belanja_52($val['BELANJA_52']);
            $d_data->set_belanja_53($val['BELANJA_53']);
            $d_data->set_belanja_54($val['BELANJA_54']);
            $d_data->set_belanja_55($val['BELANJA_55']);
            $d_data->set_belanja_56($val['BELANJA_56']);
            $d_data->set_belanja_57($val['BELANJA_57']);
            $d_data->set_belanja_58($val['BELANJA_58']);
            $d_data->set_belanja_59($val['BELANJA_59']);
			$d_data->set_belanja_61($val['BELANJA_61']);
			$d_data->set_realisasi($val['TOTAL_REALISASI']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_realisasi_belanja_modal_blu($filter) {
        Session::get('id_user');
        $sql = "select a.satker
				, substr(a.program,1,3) BA
				--, a.kppn
				--, b.nmsatker
				, c.nmsatker
				, c.rumpun
				, sum(a.budget_amt) Pagu
				, sum(a.actual_amt) Total_realisasi
				, sum(decode(a.akun,'537111',a.budget_amt,0)) pagu_51
				, sum(decode(a.akun,'537112',a.budget_amt,0)) pagu_52
				, sum(decode(a.akun,'537113',a.budget_amt,0)) pagu_53
				, sum(decode(a.akun,'537114',a.budget_amt,0)) pagu_54
				, sum(decode(a.akun,'537115',a.budget_amt,0)) pagu_55				
				, sum(decode(a.akun,'537111',a.actual_amt,0)) belanja_51
				, sum(decode(a.akun,'537112',a.actual_amt,0)) belanja_52
				, sum(decode(a.akun,'537113',a.actual_amt,0)) belanja_53
				, sum(decode(a.akun,'537114',a.actual_amt,0)) belanja_54
				, sum(decode(a.akun,'537115',a.actual_amt,0)) belanja_55				
				, sum(ENCUMBRANCE_AMT) encumbrance 
				FROM  rumpun_blu c,"
                . $this->_table4 . " a
                --t_satker_blu  b ,
				
				where 1=1
				and a.budget_type = '2' 
				--and a.satker=b.kdsatker
				and c.kdsatker	= a.satker			(+)
				and c.nmsatker is not null
				--and a.kppn=b.kdkppn
				AND SUBSTR(a.akun,1,1) in ('5','6')
				and substr(a.akun,1,3) = '537'
				--and nvl(a.budget_amt,0) + nvl(a.actual_amt,0) + nvl(a.encumbrance_amt,0) > 0
				
				"
        ;
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }

        $sql .= " group by a.satker ,c.nmsatker, substr(a.program,1,3), c.rumpun ";
        $sql .= " ORDER by a.satker ";

        //var_dump ($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['SATKER']);
            $d_data->set_kppn($val['KPPN']);
            $d_data->set_ba($val['BA']);
            $d_data->set_pagu($val['PAGU']);
            $d_data->set_dipa($val['NMSATKER']);
			$d_data->set_rumpun($val['RUMPUN']);
			$d_data->set_pagu_51($val['PAGU_51']);
            $d_data->set_pagu_52($val['PAGU_52']);
            $d_data->set_pagu_53($val['PAGU_53']);
            $d_data->set_pagu_54($val['PAGU_54']);
            $d_data->set_pagu_55($val['PAGU_55']);
            $d_data->set_pagu_56($val['PAGU_56']);
            $d_data->set_pagu_57($val['PAGU_57']);
            $d_data->set_pagu_58($val['PAGU_58']);
            $d_data->set_pagu_59($val['PAGU_59']);
			$d_data->set_pagu_61($val['PAGU_61']);
            $d_data->set_belanja_51($val['BELANJA_51']);
            $d_data->set_belanja_52($val['BELANJA_52']);
            $d_data->set_belanja_53($val['BELANJA_53']);
            $d_data->set_belanja_54($val['BELANJA_54']);
            $d_data->set_belanja_55($val['BELANJA_55']);
            $d_data->set_belanja_56($val['BELANJA_56']);
            $d_data->set_belanja_57($val['BELANJA_57']);
            $d_data->set_belanja_58($val['BELANJA_58']);
            $d_data->set_belanja_59($val['BELANJA_59']);
			$d_data->set_belanja_61($val['BELANJA_61']);
			$d_data->set_realisasi($val['TOTAL_REALISASI']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_kl_per_es1satker_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = "SELECT BAES1 KODE, C.NMES1 NAMA,  SUM(RPHREAL)* -1 REALISASI 
				FROM 
                GL_JE_LINES A, "
                . $this->_table4 . " C, 
				( SELECT DISTINCT KDSATKER, BA, BAES1 FROM T_SATKER_ALL ) B  	
				WHERE 1=1 											
				AND SUBSTR(AKUN,1,1) = '4'
				AND A.KDSATKER = B.KDSATKER				
				AND C.KDES1 = B.BAES1								
				";
        $no = 0;
        foreach ($filter as $filter1) {
            $sql .= " AND " . $filter1;
        }
        $sql .= " GROUP BY BAES1, C.NMES1 ";
		$sql .= " UNION ALL ";
		$sql .= "SELECT BAES1 || '-' || A.KDSATKER KODE, UPPER(B.NMSATKER) NAMA, SUM(RPHREAL)* -1 REALISASI 
				FROM 
                GL_JE_LINES A , 
                ( SELECT DISTINCT NMSATKER, KDSATKER, BA, BAES1 FROM T_SATKER_ALL ) B 				
				WHERE 1=1 
				AND A.KDSATKER =B.KDSATKER							
				AND SUBSTR(AKUN,1,1) = '4'				
				
				";
        $no = 0;
        foreach ($filter as $filter2) {
            $sql .= " AND " . $filter2;
        }
        $sql .= " GROUP BY BAES1, A.KDSATKER, B.NMSATKER ";
        $sql .= " ORDER BY KODE ";
        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['KODE']);
            $d_data->set_nmkegiatan($val['NAMA']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_lo_blu_harian($filter ,$filter2) {
        Session::get('id_user');
        $sql = " with LO_BLU_1 as (
					select axis_seq
					, description
					, flag
					, sum(amount_15) amount_15
					, sum(amount_14) amount_14
					,sum(amount_15)-sum(amount_14) selisih
					,case when SUM(amount_14) > 0 then round(sum(amount_15)/sum(amount_14),2)*100 else 0 end as presentase 
					,case when sum(amount_15) > sum(amount_14) then 'UP' else 'DOWN' end as updown_flag
					from 
					(SELECT DISTINCT AXIS_SEQ
					, DESCRIPTION
					, a.flag
					, SUM(CASE WHEN AMOUNT < 0 THEN AMOUNT*-1 ELSE AMOUNT END) amount_15 
					, 0 amount_14
					FROM 
					AAB_LO_BLU A
					left join
					DM_LO_BLU B
					on
					A.SEGMENT3 = B.AKUN
					where
					1=1 "  ;     
                   $no = 0;
					foreach ($filter as $filter) {
						$sql .= " AND " . $filter;
					}
					;
                 $sql .=   "group by AXIS_SEQ, DESCRIPTION, a.flag
                  
				union all                   
				SELECT DISTINCT AXIS_SEQ
				, DESCRIPTION
				, a.flag
				, 0 amount_15 
				, SUM(CASE WHEN AMOUNT < 0 THEN AMOUNT*-1 ELSE AMOUNT END) amount_14
				FROM 
				AAB_LO_BLU A
				left join
				DM_LO_BLU_14 B
				on
				A.SEGMENT3 = B.AKUN
				where
				1=1 ";
				$no = 0;
					foreach ($filter2 as $filter2) {
						$sql .= " AND " . $filter2;
					}
					;
				$sql .=   "group by AXIS_SEQ, DESCRIPTION, a.flag
				order by AXIS_SEQ
				)
				group by axis_seq, description, flag
				)
				select * from LO_BLU_1
				union all
				select
				151 as axis_seq,
				'BEBAN TOTAL' as description,
				'Y' flag,
				(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150) amount_15,
				(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150) amount_14,
				(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150) - (select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150) selisih,
				case when (select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150) > 0 then round((select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150) / (select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150),2)*100 else 0 end presentase,
				case when (select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150) > (select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150) then 'UP' else 'DOWN' end updown_flag
				from dual
				union all
				select
				152 as axis_seq,
				'SURPLUS DEFISIT (TANPA PENDAPATAN APBN)' as description,
				'Y' flag,
				(select sum(amount_15) from LO_BLU_1 where axis_seq = 134)
				-(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150) 
				amount_15,
				(select sum(amount_14) from LO_BLU_1 where axis_seq = 134)
				-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150) 
				amount_14,
				((select sum(amount_15) from LO_BLU_1 where axis_seq = 134)-(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150))-
				((select sum(amount_14) from LO_BLU_1 where axis_seq = 134)-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150)) selisih,
				case when ((select sum(amount_14) from LO_BLU_1 where axis_seq = 134)-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150)) > 0 then
				round(((select sum(amount_15) from LO_BLU_1 where axis_seq = 134)-(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150))/
				((select sum(amount_14) from LO_BLU_1 where axis_seq = 134)-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150)),2)*100 else 0 end presentase,
				case when ((select sum(amount_15) from LO_BLU_1 where axis_seq = 134)-(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150)) > 
				((select sum(amount_14) from LO_BLU_1 where axis_seq = 134)-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150)) then 'UP' else 'DOWN' end as updown_flag
				from dual
				union all
				select
				153 as axis_seq,
				'SURPLUS DEFISIT' as description,
				'Y' flag,
				(select sum(amount_15) from LO_BLU_1 where axis_seq = 140)
				-(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150) 
				amount_15,
				(select sum(amount_14) from LO_BLU_1 where axis_seq = 140)
				-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150) 
				amount_14,
				((select sum(amount_15) from LO_BLU_1 where axis_seq = 140)-(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150))-
				((select sum(amount_14) from LO_BLU_1 where axis_seq = 140)-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150)) selisih,
				case when ((select sum(amount_14) from LO_BLU_1 where axis_seq = 140)-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150)) > 0 then
				round(((select sum(amount_15) from LO_BLU_1 where axis_seq = 140)-(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150))
				/((select sum(amount_14) from LO_BLU_1 where axis_seq = 140)-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150)),2)*100 else 0 end presentase,
				case when ((select sum(amount_15) from LO_BLU_1 where axis_seq = 140)-(select sum(amount_15) from LO_BLU_1 where axis_seq between 141 and 150)) > 
				((select sum(amount_14) from LO_BLU_1 where axis_seq = 140)-(select sum(amount_14) from LO_BLU_1 where axis_seq between 141 and 150))
				then 'UP' else 'DOWN' end as updown_flag
				from dual
				order by axis_seq" 
				
				;
      

        //$sql .= " ORDER BY A.KPPN, A.AKUN ";

        // var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
			$d_data->set_deskripsi($val['DESCRIPTION']);           
             $d_data->set_jumlah_14($val['AMOUNT_14']); 
			$d_data->set_jumlah_15($val['AMOUNT_15']); 			
			$d_data->set_persentase(number_format($val['PRESENTASE']/100,2,","," "));		
			$d_data->set_flag($val['FLAG']);	
			$d_data->set_selisih($val['SELISIH']);	
			$d_data->set_updownflag($val['UPDOWN_FLAG']);			
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_saldo_kas_blu($filter) {
        Session::get('id_user');
        
        $sql = "SELECT * FROM SALDO_BLU
					"
        ;

        $no = 0;
         foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        } 

		//$sql .= " GROUP BY A.KDSATKER, A.NMSATKER, A.RUMPUN, A.SALDO_AKHIR_2014";
        $sql .= " ORDER BY KDSATKER";
        //var_dump ($sql);

        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
            $d_data->set_nmsatker($val['NMSATKER']);
			$d_data->set_rumpun($val['RUMPUN']);
			$d_data->set_saldo_awal($val['SALDO_AKHIR_2014']);
			$d_data->set_pendapatan($val['PENDAPATAN']);
			$d_data->set_belanja($val['BELANJA']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_monitoring_akun($select, $filter, $group) {
        Session::get('id_user');
        
        $sql = "SELECT  "
        ;
		$no = 0;
        foreach ($select as $select) {
            $sql .= $select .",";
        }
		$sql .= " SUM(AMOUNT) REALISASI  "
        ;
		 $sql .= "FROM DM_DAY_REALISASI_PER_SATKER A
				LEFT JOIN T_BA B
				ON A.BA = B.KDBA
				LEFT JOIN
				T_ESELON1 C
				ON A.BAES1 = C.KDES1
				WHERE 1=1
		  "
		;
        $no = 0;
         foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        } 

		foreach ($group as $group) {
            $sql .= " GROUP BY ". $group;
        }
        //$sql .= " ORDER BY KDSATKER";
        //var_dump ($sql);

        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
            $d_data->set_nmsatker($val['NMSATKER']);
			$d_data->set_rumpun($val['RUMPUN']);
			$d_data->set_akun($val['AKUN']);
			$d_data->set_realisasi($val['REALISASI']);
			$d_data->set_jenis($val['JENIS']);
			$d_data->set_ba($val['BA']);
			$d_data->set_baes1($val['BAES1']);
			$d_data->set_nama_ba($val['NMBA']);
			$d_data->set_nama_baes1($val['NMES1']);
			$d_data->set_kanwil($val['NMKANWIL']);
			$d_data->set_nama_kppn($val['NMKPPN']);
			$d_data->set_sumber_dana($val['SUMBER_DANA']);
            $data[] = $d_data;
        }
        return $data;
    }
	

    /*
     * setter
     */
	public function set_pagu($pagu) {
        $this->_pagu = $pagu;
    }
	public function set_realisasi($realisasi) {
        $this->_realisasi = $realisasi;
    }
	public function set_dipa($dipa) {
        $this->_dipa = $dipa;
    }
	public function set_satker($satker) {
        $this->_satker = $satker;
    }
	public function set_akun($akun) {
        $this->_akun = $akun;
    }
	public function set_program($program) {
        $this->_program = $program;
    }
	public function set_output($output) {
        $this->_output = $output;
    }
    public function set_nmsatker($nmsatker) {
        $this->_nmsatker = $nmsatker;
    }
	public function set_kppn($kppn) {
        $this->_kppn = $kppn;
    }
	public function set_rumpun($rumpun) {
        $this->_rumpun = $rumpun;
    }	
	public function set_januari($januari) {
        $this->_januari = $januari;
    }
	public function set_februari($februari) {
        $this->_februari = $februari;
    }
	public function set_maret($maret) {
        $this->_maret = $maret;
    }
	public function set_april($april) {
        $this->_april = $april;
    }
	public function set_mei($mei) {
        $this->_mei = $mei;
    }
	public function set_juni($juni) {
        $this->_juni = $juni;
    }
	public function set_juli($juli) {
        $this->_juli = $juli;
    }
	public function set_agustus($agustus) {
        $this->_agustus = $agustus;
    }
	public function set_september($september) {
        $this->_september = $september;
    }
	public function set_oktober($oktober) {
        $this->_oktober = $oktober;
    }
	public function set_november($november) {
        $this->_november = $november;
    }
	public function set_desember($desember) {
        $this->_desember = $desember;
    }
	public function set_invoice_num($invoice_num) {
        $this->_invoice_num = $invoice_num;
    }
	public function set_invoice_date($invoice_date) {
        $this->_invoice_date = $invoice_date;
    }
	public function set_check_number($check_number) {
        $this->_check_number = $check_number;
    }
	public function set_check_date($check_date) {
        $this->_check_date = $check_date;
    }
	public function set_check_amount($check_amount) {
        $this->_check_amount = $check_amount;
    }
	public function set_pendapatan($pendapatan) {
        $this->_pendapatan = $pendapatan;
    }
	public function set_belanja($belanja) {
        $this->_belanja = $belanja;
    }
	public function set_pagu_51($pagu_51) {
        $this->_pagu_51 = $pagu_51;
    }

    public function set_pagu_52($pagu_52) {
        $this->_pagu_52 = $pagu_52;
    }

    public function set_pagu_53($pagu_53) {
        $this->_pagu_53 = $pagu_53;
    }

    public function set_pagu_54($pagu_54) {
        $this->_pagu_54 = $pagu_54;
    }

    public function set_pagu_55($pagu_55) {
        $this->_pagu_55 = $pagu_55;
    }

    public function set_pagu_56($pagu_56) {
        $this->_pagu_56 = $pagu_56;
    }

    public function set_pagu_57($pagu_57) {
        $this->_pagu_57 = $pagu_57;
    }

    public function set_pagu_58($pagu_58) {
        $this->_pagu_58 = $pagu_58;
    }

    public function set_pagu_59($pagu_59) {
        $this->_pagu_59 = $pagu_59;
    }

    public function set_pagu_71($pagu_71) {
        $this->_pagu_71 = $pagu_71;
    }

    public function set_pagu_61($pagu_61) {
        $this->_pagu_61 = $pagu_61;
    }

    public function set_belanja_51($belanja_51) {
        $this->_belanja_51 = $belanja_51;
    }

    public function set_belanja_52($belanja_52) {
        $this->_belanja_52 = $belanja_52;
    }

    public function set_belanja_53($belanja_53) {
        $this->_belanja_53 = $belanja_53;
    }

    public function set_belanja_54($belanja_54) {
        $this->_belanja_54 = $belanja_54;
    }

    public function set_belanja_55($belanja_55) {
        $this->_belanja_55 = $belanja_55;
    }

    public function set_belanja_56($belanja_56) {
        $this->_belanja_56 = $belanja_56;
    }

    public function set_belanja_57($belanja_57) {
        $this->_belanja_57 = $belanja_57;
    }

    public function set_belanja_58($belanja_58) {
        $this->_belanja_58 = $belanja_58;
    }

    public function set_belanja_59($belanja_59) {
        $this->_belanja_59 = $belanja_59;
    }

    public function set_belanja_71($belanja_71) {
        $this->_belanja_71 = $belanja_71;
    }

    public function set_belanja_61($belanja_61) {
        $this->_belanja_61 = $belanja_61;
    }

	 public function set_deskripsi($deskripsi) {
        $this->_deskripsi = $deskripsi;
    }
	public function set_jumlah_15($jumlah_15) {
        $this->_jumlah_15 = $jumlah_15;
    }
	public function set_jumlah_14($jumlah_14) {
        $this->_jumlah_14 = $jumlah_14;
    }
	public function set_flag($flag) {
        $this->_flag = $flag;
    }
	public function set_updownflag($updownflag) {
        $this->_updownflag = $updownflag;
    }
	public function set_persentase($persentase) {
        $this->_persentase = $persentase;
    }
	public function set_selisih($selisih) {
        $this->_selisih = $selisih;
    }
	public function set_saldo_awal($saldo_awal) {
        $this->_saldo_awal = $saldo_awal;
    }
	public function set_saldo_akhir($saldo_akhir) {
        $this->_saldo_akhir = $saldo_akhir;
    }
	public function set_ba($ba) {
        $this->_ba = $ba;
    }
	public function set_baes1($baes1) {
        $this->_baes1 = $baes1;
    }
	public function set_nama_ba($nama_ba) {
        $this->_nama_ba = $nama_ba;
    }
	public function set_nama_baes1($nama_baes1) {
        $this->_nama_baes1 = $nama_baes1;
    }
	public function set_kanwil($kanwil) {
        $this->_kanwil = $kanwil;
    }
	public function set_nama_kppn($nama_kppn) {
        $this->_nama_kppn = $nama_kppn;
    }

	public function set_jenis($jenis) {
        $this->_jenis = $jenis;
    }
	public function set_sumber_dana($sumber_dana) {
        $this->_sumber_dana = $sumber_dana;
    }
    /*
     * getter
     */
	public function get_ba() {
        return $this->_ba;
    }
	public function get_realisasi() {
        return $this->_realisasi;
    }
	public function get_satker() {
        return $this->_satker;
    }
	public function get_akun() {
        return $this->_akun;
    }
	public function get_program() {
        return $this->_program;
    }
	public function get_output() {
        return $this->_output;
    }
	public function get_kppn() {
        return $this->_kppn;
    }
	public function get_rumpun() {
        return $this->_rumpun;
    }
    public function get_nmsatker() {
        return $this->_nmsatker;
    }	
	public function get_januari() {
        return $this->_januari;
    }
	public function get_februari() {
        return $this->_februari;
    }
	public function get_maret() {
        return $this->_maret;
    }
	public function get_april() {
        return $this->_april;
    }
	public function get_mei() {
        return $this->_mei;
    }
	public function get_juni() {
        return $this->_juni;
    }
	public function get_juli() {
        return $this->_juli;
    }
	public function get_agustus() {
        return $this->_agustus;
    }
	public function get_september() {
        return $this->_september;
    }
	public function get_oktober() {
        return $this->_oktober;
    }
	public function get_november() {
        return $this->_november;
    }
	public function get_desember() {
        return $this->_desember;
    }
	public function get_invoice_num() {
        return $this->_invoice_num;
    }
	public function get_invoice_date() {
        return $this->_invoice_date;
    }
	public function get_check_number() {
        return $this->_check_number;
    }
	public function get_check_date() {
        return $this->_check_date;
    }
	public function get_check_amount() {
        return $this->_check_amount;
    }
	public function get_pendapatan() {
        return $this->_pendapatan;
    }
	public function get_belanja() {
        return $this->_belanja;
    }
	public function get_pagu_51() {
        return $this->_pagu_51;
    }

    public function get_pagu_52() {
        return $this->_pagu_52;
    }

    public function get_pagu_53() {
        return $this->_pagu_53;
    }

    public function get_pagu_54() {
        return $this->_pagu_54;
    }

    public function get_pagu_55() {
        return $this->_pagu_55;
    }

    public function get_pagu_56() {
        return $this->_pagu_56;
    }

    public function get_pagu_57() {
        return $this->_pagu_57;
    }

    public function get_pagu_58() {
        return $this->_pagu_58;
    }

    public function get_pagu_59() {
        return $this->_pagu_59;
    }

    public function get_pagu_71() {
        return $this->_pagu_71;
    }

    public function get_pagu_61() {
        return $this->_pagu_61;
    }

    public function get_belanja_51() {
        return $this->_belanja_51;
    }

    public function get_belanja_52() {
        return $this->_belanja_52;
    }

    public function get_belanja_53() {
        return $this->_belanja_53;
    }

    public function get_belanja_54() {
        return $this->_belanja_54;
    }

    public function get_belanja_55() {
        return $this->_belanja_55;
    }

    public function get_belanja_56() {
        return $this->_belanja_56;
    }

    public function get_belanja_57() {
        return $this->_belanja_57;
    }

    public function get_belanja_58() {
        return $this->_belanja_58;
    }

    public function get_belanja_59() {
        return $this->_belanja_59;
    }

    public function get_belanja_71() {
        return $this->_belanja_71;
    }

    public function get_belanja_61() {
        return $this->_belanja_61;
    }
	public function get_pagu() {
        return $this->_pagu;
    }
	public function get_dipa() {
        return $this->_dipa;
    }
		public function get_deskripsi() {
        return $this->_deskripsi;
    }
	public function get_jumlah_14() {
        return $this->_jumlah_14;
    }
	public function get_jumlah_15() {
        return $this->_jumlah_15;
    }
	
	public function get_persentase() {
        return $this->_persentase;
    }
	public function get_selisih() {
        return $this->_selisih;
    }
	public function get_flag() {
        return $this->_flag;
    }
	public function get_saldo_awal() {
        return $this->_saldo_awal;
    }
	public function get_saldo_akhir() {
        return $this->_saldo_akhir;
    }
	public function get_updownflag() {
        return $this->_updownflag;
    }

	public function get_baes1() {
        return $this->_baes1;
    }
	public function get_nama_ba() {
        return $this->_nama_ba;
    }
	public function get_nama_baes1() {
        return $this->_nama_baes1;
    }
	public function get_jenis() {
        return $this->_jenis;
    }
	public function get_kanwil() {
        return $this->_kanwil;
    }
	public function get_nama_kppn() {
        return $this->_nama_kppn;
    }
	public function get_sumber_dana() {
        return $this->_sumber_dana;
    }
	
	
	public function get_table1() {
        return $this->_table1;
    }
	public function get_table2() {
        return $this->_table2;
    }
	public function get_table3() {
        return $this->_table3;
    }
	public function get_table4() {
        return $this->_table4;
    }
    /*
     * destruktor
     */

    public function __destruct() {
        
    }

}
